//
//  KADataBase.m
//  KeepAccounts
//
//  Created by honeyrr on 2017/10/6.
//  Copyright © 2017年 honeyrr. All rights reserved.
//

#import "KADataBase.h"
#import <sqlite3.h>

@implementation KADataBase

//单例
static id _instance;
+ (KADataBase *)instance  {
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        _instance = [[self alloc]init];
    });
    return _instance;
}

-(void) createDataBaseWithTable
{
    //获取Document文件夹下的数据库文件，没有则创建
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"keepAccounts.db"];
    //获取数据库并打开
    FMDatabase *database  = [FMDatabase databaseWithPath:dbPath];
    if (![database open]) {
        NSLog(@"Open database failed");
        return;
    }
    //创建表（FMDB中只有update和query操作，出了查询其他都是update操作）
    [database executeUpdate:@"create table keepaccounts (billtype text, name text, money text, imageName text, date text, time text, remark text)"];
    [database close];
}

-(FMDatabase *) database
{
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"keepAccounts.db"];
    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    return database;
}

//增
-(void) addDataType:(NSString *)billtype name:(NSString *)name money:(NSString *)money imageName:(NSString *)imageName date:(NSString *)date time:(NSString *)time remark:(NSString *)remark
{
    FMDatabase *database = [self database];
    if (![database open]) {
        [SVProgressHUD showErrorWithStatus:@"数据加载失败..."];
        return;
    }
    //FMDatabase *database = [self database];
    //[database executeUpdate:@"create table keepaccounts (billtype text, name text, money integer, imageName text, date text, remark text)"];
    BOOL isInsert = [database executeUpdate:@"insert into keepaccounts values (?,?,?,?,?,?,?)", billtype, name, money, imageName, date, time, remark];
    if (isInsert) {
        [SVProgressHUD showSuccessWithStatus:@"账单创建成功!"];
    } else {
        [SVProgressHUD showErrorWithStatus:@"账单创建失败!"];
    }
    [database close];
}
//删
-(void) deleteDataWithDate:(NSString *)date
{
    FMDatabase *database = [self database];
    if (![database open]) {
        [SVProgressHUD showErrorWithStatus:@"数据加载失败..."];
        return;
    }
    BOOL isDelete = [database executeUpdate:@"delete from keepaccounts where date = ?",date];
    if (isDelete) {
        [SVProgressHUD showSuccessWithStatus:@"删除成功!"];
    } else {
        [SVProgressHUD showErrorWithStatus:@"删除失败!"];
    }
    [database close];
}
//改
-(void) updateDataType:(NSString *)billtype name:(NSString *)name money:(NSString *)money imageName:(NSString *)imageName date:(NSString *)date time:(NSString *)time remark:(NSString *)remark
{
    FMDatabase *database = [self database];
    if (![database open]) {
        [SVProgressHUD showErrorWithStatus:@"数据加载失败..."];
        return;
    }
    BOOL isUpdate = [database executeUpdate:@"update keepaccounts set billtype = ?, name = ?, money = ?, imageName = ?, date = ?, time = ?, remark = ? where time = ?", billtype, name, money, imageName, date, time, remark];
    if (isUpdate) {
        [SVProgressHUD showSuccessWithStatus:@"更新成功!"];
    } else {
        [SVProgressHUD showErrorWithStatus:@"更新失败!"];
    }
    [database close];
}
//查
-(NSMutableArray *) queryWithDate:(NSString *)date
{
    FMDatabase *database = [self database];
    if (![database open]) {
        [SVProgressHUD showErrorWithStatus:@"数据加载失败..."];
        NSLog(@"open file");
        return nil;
    }
    FMResultSet *resultSet = [database executeQuery:@"select * from keepaccounts where date = ?",date];
    NSMutableArray *billMarr = [NSMutableArray array];
    while ([resultSet next]) {
        NSString *billtype = [resultSet stringForColumn:@"billtype"];
        NSString *name = [resultSet stringForColumn:@"name"];
        NSString *money = [resultSet stringForColumn:@"money"];
        NSString *imageName = [resultSet stringForColumn:@"imageName"];
        NSString *date = [resultSet stringForColumn:@"date"];
        NSString *time = [resultSet stringForColumn:@"time"];
        NSString *remark = [resultSet stringForColumn:@"remark"];
        
        NSDictionary *dict = @{@"billtype":billtype, @"name":name, @"money":money, @"imageName":imageName, @"date":date, @"time":time, @"remark":remark};
        [billMarr addObject:dict];
    }
    [database close];
    return billMarr;
}

//条件查询
-(NSMutableArray *) queryWithDate:(NSString *)date otherWhere:(NSString *)other
{
    FMDatabase *database = [self database];
    if (![database open]) {
        [SVProgressHUD showErrorWithStatus:@"数据加载失败..."];
        NSLog(@"open file");
        return nil;
    }
    FMResultSet *resultSet = [database executeQuery:@"select * from keepaccounts where date = ? and billtype = ?", date, other];
    NSMutableArray *billMarr = [NSMutableArray array];
    while ([resultSet next]) {
        NSString *billtype = [resultSet stringForColumn:@"billtype"];
        NSString *name = [resultSet stringForColumn:@"name"];
        NSString *money = [resultSet stringForColumn:@"money"];
        NSString *imageName = [resultSet stringForColumn:@"imageName"];
        NSString *date = [resultSet stringForColumn:@"date"];
        NSString *time = [resultSet stringForColumn:@"time"];
        NSString *remark = [resultSet stringForColumn:@"remark"];
        
        NSDictionary *dict = @{@"billtype":billtype, @"name":name, @"money":money, @"imageName":imageName, @"date":date, @"time":time, @"remark":remark};
        [billMarr addObject:dict];
    }
    [database close];
    return billMarr;
}

-(NSMutableArray *) queryWithBilltype:(NSString *)billtype
{
    FMDatabase *database = [self database];
    if (![database open]) {
        [SVProgressHUD showErrorWithStatus:@"数据加载失败..."];
        NSLog(@"open file");
        return nil;
    }
    FMResultSet *resultSet = [database executeQuery:@"select * from keepaccounts where billtype = ?", billtype];
    NSMutableArray *billMarr = [NSMutableArray array];
    while ([resultSet next]) {
        NSString *billtype = [resultSet stringForColumn:@"billtype"];
        NSString *name = [resultSet stringForColumn:@"name"];
        NSString *money = [resultSet stringForColumn:@"money"];
        NSString *imageName = [resultSet stringForColumn:@"imageName"];
        NSString *date = [resultSet stringForColumn:@"date"];
        NSString *time = [resultSet stringForColumn:@"time"];
        NSString *remark = [resultSet stringForColumn:@"remark"];
        
        NSDictionary *dict = @{@"billtype":billtype, @"name":name, @"money":money, @"imageName":imageName, @"date":date, @"time":time, @"remark":remark};
        [billMarr addObject:dict];
    }
    [database close];
    return billMarr;
}

-(NSMutableArray *)queryWithAllDate
{
    FMDatabase *database = [self database];
    if (![database open]) {
        [SVProgressHUD showErrorWithStatus:@"数据加载失败..."];
        NSLog(@"open file");
        return nil;
    }
    FMResultSet *resultSet = [database executeQuery:@"select * from keepaccounts"];
    NSMutableArray *billMarr = [NSMutableArray array];
    while ([resultSet next]) {
        NSString *billtype = [resultSet stringForColumn:@"billtype"];
        NSString *name = [resultSet stringForColumn:@"name"];
        NSString *money = [resultSet stringForColumn:@"money"];
        NSString *imageName = [resultSet stringForColumn:@"imageName"];
        NSString *date = [resultSet stringForColumn:@"date"];
        NSString *time = [resultSet stringForColumn:@"time"];
        NSString *remark = [resultSet stringForColumn:@"remark"];
        
        NSDictionary *dict = @{@"billtype":billtype, @"name":name, @"money":money, @"imageName":imageName, @"date":date, @"time":time, @"remark":remark};
        [billMarr addObject:dict];
    }
    [database close];
    return billMarr;
}

-(void)clearDataBase
{
    //;
    FMDatabase *database = [self database];
    if (![database open]) {
        [SVProgressHUD showErrorWithStatus:@"数据加载失败..."];
        return;
    }
    BOOL isUpdate = [database executeUpdate:@"delete from keepaccounts"];
    if (isUpdate) {
        [SVProgressHUD showSuccessWithStatus:@"清空成功!"];
    } else {
        [SVProgressHUD showErrorWithStatus:@"清空失败!"];
    }
    [database close];
}
@end
